from operator import and_

from sqlalchemy import Column, ForeignKey, Integer, String, Date
from sqlalchemy.orm import relationship, object_session, Session

from .db import Base


class ParentToChildrenTable(Base):
    __tablename__ = "parent_to_children"
    children_id = Column(Integer, ForeignKey("users.id"), primary_key=True)
    parent_id = Column(Integer, ForeignKey("users.id"), primary_key=True)


class Country(Base):
    __tablename__ = "countries"
    id = Column(Integer, primary_key=True)
    name = Column(String, index=True)

    regions = relationship("Region", back_populates="country")


class Region(Base):
    __tablename__ = "regions"
    id = Column(Integer, primary_key=True)
    country_id = Column(Integer, ForeignKey("countries.id"))
    name = Column(String, index=True)

    country = relationship("Country", back_populates="regions")
    places = relationship("Place", back_populates="region")


class PlaceType(Base):
    __tablename__ = "place_types"
    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)


class Place(Base):
    __tablename__ = "places"
    id = Column(Integer, primary_key=True)
    region_id = Column(Integer, ForeignKey("regions.id"))
    name = Column(String, index=True)
    place_type_id = Column(Integer, ForeignKey("place_types.id"))

    region = relationship("Region", back_populates="places")
    place_type = relationship("PlaceType")


class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    first_name = Column(String, index=True)
    last_name = Column(String, index=True)
    middle_name = Column(String, nullable=True)
    birth_day = Column(Date, nullable=True)
    birth_place_id = Column(Integer, ForeignKey("places.id"), nullable=True)
    spouse_id = Column(Integer, ForeignKey("users.id"), nullable=True)
    marriage_date = Column(Date, nullable=True)
    death_day = Column(Date, nullable=True)
    death_place_id = Column(Integer, ForeignKey("places.id"), nullable=True)
    # email = Column(String, unique=True, index=True)
    # hashed_password = Column(String)
    # is_active = Column(Boolean, default=True)
    children = relationship(
        "User",
        secondary=ParentToChildrenTable.__table__,
        primaryjoin="User.id == ParentToChildrenTable.parent_id",
        secondaryjoin="User.id == ParentToChildrenTable.children_id",
        backref="parents",
        lazy='selectin',
        sync_backref=True
    )
    birth_place = relationship("Place", foreign_keys=[birth_place_id])
    death_place = relationship("Place", foreign_keys=[death_place_id])

    @property
    def siblings(self):
        user_id = self.id
        parents = [u.id for u in self.parents]
        session: Session = object_session(self)
        siblings_ids = session.query(ParentToChildrenTable.children_id).where(
            and_(
                ParentToChildrenTable.children_id != user_id,
                ParentToChildrenTable.parent_id.in_(parents)
            )
        )
        return session.query(User).filter(User.id.in_(siblings_ids))
